Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# EXECUTE IMMEDIATE Statement

The `EXECUTE` `IMMEDIATE` statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. For more information, see "Using the EXECUTE IMMEDIATE Statement in PL/SQL".

Syntax

Description of the illustration execute_immediate_statement.gif

Keyword and Parameter Description

bind_argument

An expression whose value is passed to the dynamic SQL statement, or a variable that stores a value returned by the dynamic SQL statement.

BULK COLLECT

Stores result values in one or more collections, for faster queries than loops with `FETCH` statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".

collection_name

A declared collection into which `select_item` values are fetched. For each `select_item`, there must be a corresponding, type-compatible collection in the list.

host_array_name

An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which `select_item` values are fetched. For each `select_item`, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

define_variable

A variable that stores a selected column value.

dynamic_string

A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type `CHAR` or `VARCHAR2`, not `NCHAR` or `NVARCHAR2`.

INTO ...

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the `INTO` clause.

record_name

A user-defined or `%ROWTYPE` record that stores a selected row.

returning_clause

Returns values from inserted rows, eliminating the need to `SELECT` the rows afterward. You can retrieve the column values into variables or into collections. You cannot use the `RETURNING` clause for remote or parallel inserts. If the statement does not affect any rows, the values of the variables specified in the `RETURNING` clause are undefined. For the syntax of `returning_clause`, see the "RETURNING INTO Clause".

USING ...

Specifies a list of input and/or output bind arguments. The parameter mode defaults to `IN`.

Usage Notes

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders for bind arguments. You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

You can place all bind arguments in the `USING` clause. The default parameter mode is `IN`. For DML statements that have a `RETURNING` clause, you can place `OUT` arguments in the `RETURNING` `INTO` clause without specifying the parameter mode, which, by definition, is `OUT`. If you use both the `USING` clause and the `RETURNING` `INTO` clause, the `USING` clause can contain only `IN` arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the `USING` clause and/or `RETURNING` `INTO` clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (`TRUE`, `FALSE`, and `NULL`). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".

Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, `LOB`s, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be `BOOLEAN`s or index-by tables. The only exception is that a PL/SQL record can appear in the `INTO` clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because `EXECUTE` `IMMEDIATE` re-prepares the dynamic string before every execution.

The string argument to the `EXECUTE IMMEDIATE` command cannot be one of the national character types, such as `NCHAR` or `NVARCHAR2`.

Note: When using dynamic SQL with PL/SQL, be aware of the risks of SQL injection, which is a possible security issue. For more information on SQL injection and possible problems, see Oracle Database Application Developer's Guide - Fundamentals. You can also search for "SQL injection" on the Oracle Technology Network at `http://www.oracle.com/technology/`

Examples

For examples, see the following:

Example 7-1, "Examples of Dynamic SQL"
Example 7-2, "Dynamic SQL Procedure that Accepts Table Name and WHERE Clause"
Example 7-4, "Dynamic SQL with BULK COLLECT INTO Clause"
Example 7-5, "Dynamic SQL with RETURNING BULK COLLECT INTO Clause"
Example 7-6, "Dynamic SQL Inside FORALL Statement"

Related Topics

"OPEN-FOR Statement"